import re
import pandas as pd
from sqlalchemy import create_engine

def str_split(job_str):
    job_str = job_str.replace(" ", "")
    # 定义一个正则表达式，其中'|'表示或，用于匹配多个分隔符
    split_pattern = re.compile(r',|/|，|、|\n|-')  # 这里也包括了中文的逗号和顿号，根据实际需要可调整
    # 使用re.split进行分割
    keywords = split_pattern.split(job_str)

    # 去除空字符串
    keywords = [keyword for keyword in keywords if keyword]
    # 将关键词列表合并成一个字符串，这里以英文逗号作为分隔符
    result_str = ','.join(keywords)
    return result_str
def get_year(job_str):
    year_str=job_str['资历']
    work_experience=job_str['工作经验']
    # 匹配数字，直到遇到'年'字，但不包括'年'字本身
    pattern = r'(\d+)(?=\D*年)'
    matches = re.findall(pattern, work_experience)
    valid_matches = [int(match) for match in matches if int(match) <= 15]
    if len(matches)==0 or len(valid_matches)== 0:
        if year_str=='经验不限':
            return 0
        else:
            range_match = re.search(r'(\d+-\d+)', year_str)
            if range_match:
                salary_str = range_match.group(0)
                salary_parts = salary_str.split('-')
                if len(salary_parts) == 1:
                    lower = int(salary_parts[0])
                else:
                    lower, _ = map(int, salary_parts)
                return lower
            else:  # 如果没有找到薪资范围，则尝试匹配单一数值
                single_match = re.search(r'(\d+)(?=\D*年)', year_str)
                if single_match:
                    lower = int(single_match.group(0))
                else:
                    lower = 0
                return lower
    else:
        return int(max(valid_matches))


if __name__ == '__main__':
    csv_name=""
    combined_df_tongyi = pd.read_csv(csv_name)




    company_df = combined_df_tongyi[
        ['company_name', 'company_intro', 'company_status', 'company_size', 'company_detailed_address', 'company_type']]
    # 根据'company_detailed_address'列进行去重
    unique_company_df = company_df.drop_duplicates(subset='company_detailed_address')
    # 在unique_company_df中新增一列company_id，该列的值从1开始递增
    unique_company_df.insert(0, 'company_id', range(1, len(unique_company_df) + 1))
    unique_company_df['phone'] = '无'



    # 去掉nan值
    combined_df_tongyi.fillna("无", inplace=True)
    combined_df_tongyi['角色定位'] = combined_df_tongyi['角色定位'].apply(str_split)
    combined_df_tongyi['技术要求'] = combined_df_tongyi['技术要求'].apply(str_split)
    combined_df_tongyi['工作领域'] = combined_df_tongyi['工作领域'].apply(str_split)
    test_year = combined_df_tongyi[['资历', '工作经验']]
    test_year.loc[:, 'year'] = test_year.apply(get_year, axis=1)
    merged_df = pd.merge(combined_df_tongyi, test_year['year'], how='outer', left_index=True, right_index=True)

    cols_mapping = {
        'job_id': 'jobId',
        'company_id': 'companyId',
        'company_name': 'companyName',
        'job_title': 'name',
        'hr_name': 'hr',
        'job_salary': 'salary',
        'annual_salary': 'salaryYear',
        'tags': 'tags',
        'job_description': 'description',
        '学历': 'degree',
        '资历': 'experience',
        'company_brief_address': 'location',
        'deleted': 'deleted',
        '角色定位': 'workRole',
        '技术要求': 'workRequirements',
        '工作领域': 'workField',
        '工作经验': 'workExperience',
        'year': 'workYear'
    }

    df_renamed = merged_df.rename(columns=cols_mapping)


    database_url = 'mysql+pymysql://qwy:190601@localhost:3306/jobs'  # 数据库URL，根据实际情况修改

    # 创建数据库引擎
    engine = create_engine(database_url)

    df_renamed.to_sql(
        name='job',
        con=engine,
        if_exists='append',
        index=False,
    )






